﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data.SqlClient;
using System.Data;
using EntLibEC.Cmn;
using EntLibEC.Info.Online;

namespace EntLibEC.Dac.Online
{
    public class OnlineListAreaDac
    {
        public int Insert(OnlineListAreaInfo oParam)
        {
            string strInsert = @" Insert into Ent_OnlineListArea (ListAreaName, ListAreaKey, Category1SysNo, PagePosition, CreateUserSysNo, CreateTime, ListOrder) 
                    values(@ListAreaName, @ListAreaKey, @Category1SysNo, @PagePosition, @CreateUserSysNo, @CreateTime, @ListOrder)
                ;set @SysNo = SCOPE_IDENTITY();";

            SqlCommand cmd = new SqlCommand(strInsert);

            SqlParameter paramSysNo = new SqlParameter("@SysNo", SqlDbType.Int, 4);
            SqlParameter paramListAreaName = new SqlParameter("@ListAreaName", SqlDbType.NVarChar, 50);
            SqlParameter paramListAreaKey = new SqlParameter("@ListAreaKey", SqlDbType.Int, 4);
            SqlParameter paramCategory1SysNo = new SqlParameter("@Category1SysNo", SqlDbType.Int, 4);
            SqlParameter paramPagePosition = new SqlParameter("@PagePosition", SqlDbType.Int, 4);
            SqlParameter paramCreateUserSysNo = new SqlParameter("@CreateUserSysNo", SqlDbType.Int, 4);
            SqlParameter paramCreateTime = new SqlParameter("@CreateTime", SqlDbType.DateTime);
            SqlParameter paramListOrder = new SqlParameter("@ListOrder", SqlDbType.NVarChar, 20);

            paramSysNo.Direction = ParameterDirection.Output;

            if (oParam.ListAreaName != AppConst.StringNull)
                paramListAreaName.Value = oParam.ListAreaName;
            else
                paramListAreaName.Value = System.DBNull.Value;

            if (oParam.ListAreaKey != AppConst.IntNull)
                paramListAreaKey.Value = oParam.ListAreaKey;
            else
                paramListAreaKey.Value = System.DBNull.Value;

            if (oParam.Category1SysNo != AppConst.IntNull)
                paramCategory1SysNo.Value = oParam.Category1SysNo;
            else
                paramCategory1SysNo.Value = System.DBNull.Value;

            if (oParam.PagePosition != AppConst.IntNull)
                paramPagePosition.Value = oParam.PagePosition;
            else
                paramPagePosition.Value = System.DBNull.Value;

            if (oParam.CreateUserSysNo != AppConst.IntNull)
                paramCreateUserSysNo.Value = oParam.CreateUserSysNo;
            else
                paramCreateUserSysNo.Value = System.DBNull.Value;
            if (oParam.CreateTime != AppConst.DateTimeNull)
                paramCreateTime.Value = oParam.CreateTime;
            else
                paramCreateTime.Value = System.DBNull.Value;
            if (oParam.ListOrder != AppConst.StringNull)
                paramListOrder.Value = oParam.ListOrder;
            else
                paramListOrder.Value = System.DBNull.Value;

            cmd.Parameters.Add(paramSysNo);
            cmd.Parameters.Add(paramListAreaName);
            cmd.Parameters.Add(paramListAreaKey);
            cmd.Parameters.Add(paramCategory1SysNo);
            cmd.Parameters.Add(paramPagePosition);
            cmd.Parameters.Add(paramCreateUserSysNo);
            cmd.Parameters.Add(paramCreateTime);
            cmd.Parameters.Add(paramListOrder);

            return SqlHelper.ExecuteNonQuery(cmd, out oParam.SysNo);
        }

        public int Update(OnlineListAreaInfo oParam)
        {
            string strUpdate = @"Update Ent_OnlineListArea set ListAreaName=@ListAreaName, ListAreaKey=@ListAreaKey, Category1SysNo=@Category1SysNo, 
                    PagePosition=@PagePosition, CreateUserSysNo=@CreateUserSysNo, CreateTime=@CreateTime, ListOrder=@ListOrder
                    where SysNo=@SysNo";

            SqlCommand cmd = new SqlCommand(strUpdate);

            SqlParameter paramSysNo = new SqlParameter("@SysNo", SqlDbType.Int, 4);
            SqlParameter paramListAreaName = new SqlParameter("@ListAreaName", SqlDbType.NVarChar, 50);
            SqlParameter paramListAreaKey = new SqlParameter("@ListAreaKey", SqlDbType.Int, 4);
            SqlParameter paramCategory1SysNo = new SqlParameter("@Category1SysNo", SqlDbType.Int, 4);
            SqlParameter paramPagePosition = new SqlParameter("@PagePosition", SqlDbType.Int, 4);
            SqlParameter paramCreateUserSysNo = new SqlParameter("@CreateUserSysNo", SqlDbType.Int, 4);
            SqlParameter paramCreateTime = new SqlParameter("@CreateTime", SqlDbType.DateTime);
            SqlParameter paramListOrder = new SqlParameter("@ListOrder", SqlDbType.NVarChar, 20);

            paramSysNo.Value = oParam.SysNo;

            if (oParam.ListAreaName != AppConst.StringNull)
                paramListAreaName.Value = oParam.ListAreaName;
            else
                paramListAreaName.Value = System.DBNull.Value;

            if (oParam.ListAreaKey != AppConst.IntNull)
                paramListAreaKey.Value = oParam.ListAreaKey;
            else
                paramListAreaKey.Value = System.DBNull.Value;

            if (oParam.Category1SysNo != AppConst.IntNull)
                paramCategory1SysNo.Value = oParam.Category1SysNo;
            else
                paramCategory1SysNo.Value = System.DBNull.Value;

            if (oParam.PagePosition != AppConst.IntNull)
                paramPagePosition.Value = oParam.PagePosition;
            else
                paramPagePosition.Value = System.DBNull.Value;

            if (oParam.CreateUserSysNo != AppConst.IntNull)
                paramCreateUserSysNo.Value = oParam.CreateUserSysNo;
            else
                paramCreateUserSysNo.Value = System.DBNull.Value;
            if (oParam.CreateTime != AppConst.DateTimeNull)
                paramCreateTime.Value = oParam.CreateTime;
            else
                paramCreateTime.Value = System.DBNull.Value;
            if (oParam.ListOrder != AppConst.StringNull)
                paramListOrder.Value = oParam.ListOrder;
            else
                paramListOrder.Value = System.DBNull.Value;

            cmd.Parameters.Add(paramSysNo);
            cmd.Parameters.Add(paramListAreaName);
            cmd.Parameters.Add(paramListAreaKey);
            cmd.Parameters.Add(paramCategory1SysNo);
            cmd.Parameters.Add(paramPagePosition);
            cmd.Parameters.Add(paramCreateUserSysNo);
            cmd.Parameters.Add(paramCreateTime);
            cmd.Parameters.Add(paramListOrder);

            return SqlHelper.ExecuteNonQuery(cmd);
        }


        /// <summary>
        ///  如果ListAreaKey 值发生更新，同步更新Ent_OnlistList 表中的ListArea 字段值
        /// </summary>
        /// <param name="oParam"></param>
        public void UpdateListArea(OnlineListAreaInfo oParam)
        {
            string strSelect = "Select ListAreaKey from Ent_OnlineListArea with (nolock) where sysno=" + oParam.SysNo.ToString();

            int oldListAreaKey = Convert.ToInt32(SqlHelper.ExecuteScalar(strSelect));

            if (oldListAreaKey == oParam.ListAreaKey)
                return;
            else if(ExistProductInArea(oldListAreaKey))
            {
                string strUpdate = "Update Ent_OnlineList set ListArea=" + oParam.ListAreaKey.ToString() + " where ListArea=" + oldListAreaKey.ToString();
                SqlHelper.ExecuteNonQuery(strUpdate);
            }
        }

        public int Delete(int sysno)
        {
            string sql = "delete from Ent_OnlineListArea where sysno = " + sysno;
            SqlCommand cmd = new SqlCommand(sql);
            return SqlHelper.ExecuteNonQuery(cmd);
        }

        public bool ExistProductInArea(int listAreaKey)
        {
            string strSelect = "Select top 1 * from Ent_OnlineList with (nolock) where ListArea=" + listAreaKey.ToString();

            if (Util.HasMoreRow(SqlHelper.ExecuteDataSet(strSelect)))
                return true;
            else
                return false;
        }
    }
}
